//
//  SqliteManager.swift
//  FMDBDemo
//
//  Created by Apple on 16/12/13.
//  Copyright © 2016年 Apple. All rights reserved.
//

import UIKit

//  数据库路径
private let DBPath = (NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).last! as NSString).appendingPathComponent("SinaWeibo.db")
//  数据库操作工具类
class SqliteManager: NSObject {

 
    
    //  创建数据库操作队列
    lazy var queue: FMDatabaseQueue = FMDatabaseQueue(path: DBPath)
   
    
    
    //  单例全局访问点
    static let sharedManager: SqliteManager = SqliteManager()
    //  构造函数私有化
    private override init() {
        super.init()
        
       print(DBPath)
        
        createTables()
    }
    
    
    //  创建表
    private func createTables() {
        //  获取db.sql文件路径
        let sqlPath = Bundle.main.path(forResource: "db.sql", ofType: nil)!
        //  获取文件里面的创表语句
        let sql = try! String(contentsOfFile: sqlPath)
        
        //  执行sql
        queue.inDatabase { (db) in
            let result = db?.executeStatements(sql)
            if result == true {
                print("创表成功")
            } else {
                print("创表失败")
            }
        }    
    }
    
    //  插入数据
    func insert() {
        //  定义sql
        let sql = "INSERT INTO T_PERSON(NAME, AGE) VALUES(?, ?)"
        //  执行sql
        queue.inDatabase { (db) in
            let result = db?.executeUpdate(sql, withArgumentsIn: ["杨钰莹", 18])
            if result == true {
                print("插入成功")
            } else {
                print("插入失败")
            }
        }
    
    
    }
    
    //  查询数据
    func select() {
        
        //  定义sql
        let sql = "SELECT ID, NAME, AGE FROM T_PERSON"
        //  执行sql
        queue.inDatabase { (db) in
            if let resultSet = db?.executeQuery(sql, withArgumentsIn: nil) {
                
                //  遍历结果集,获取数据
                while resultSet.next() {
                    
                    //  获取id
                    let id = resultSet.int(forColumn: "ID")
                    //  获取name
                    let name = resultSet.string(forColumn: "NAME")!
                    //  获取age
                    let age = resultSet.int(forColumn: "AGE")
                    
                    print(id, name, age)
                    
                    
                    
                }
                
                
            }
            
        }
    
    }
    
    
    //  根据指定的sql语句查询结果
    func selectDicArray(sql: String) -> [[String: Any]] {
        
        var tempArray = [[String: Any]]()
        
        queue.inDatabase { (db) in
            if let resultSet = db?.executeQuery(sql, withArgumentsIn: nil) {
            
                while resultSet.next() {
                    //  获取到一条数据
                    var dic = [String: Any]()
                    
                    //  获取列数
                    let colCount = resultSet.columnCount()
                    
                    //  遍历列数,根据列数索引获取对应的列名和列值
                    for i in 0..<colCount {
                        
                        //  获取列名
                        let key = resultSet.columnName(for: i)!
                        //  获取列值
                        let value = resultSet.object(forColumnIndex: i)!
                        
                        //  添加键值对
                        dic[key] = value
                        
                        
                    }
                    
                    //  把字典添加到数组里面
                    tempArray.append(dic)
                    
                    
                }
                
            }
        }
        return tempArray
    
    }
    
    //  修改数据
    func update() {
        //  定义sql
        let sql = "UPDATE T_PERSON SET NAME = ?, AGE = ? WHERE ID = ?"
        
        //  执行sql
        queue.inDatabase { (db) in
            let result = db?.executeUpdate(sql, withArgumentsIn: ["赵丽颖", 20, 2])
            if result == true {
                print("修改成功")
            } else {
                print("修改失败")
            }
        }
    
    }
    
    //  删除数据
    func delete() {
        //  定义sql
        let sql = "DELETE FROM T_PERSON WHERE ID = ?"
        //  执行sql
        queue.inDatabase { (db) in
            let result = db?.executeUpdate(sql, withArgumentsIn: [3])
            
            if result == true {
                print("删除成功")
            } else {
                print("删除失败")
            }
        }
        
    
    }
    
    
    
    
    
    
    
    
}
